using AdminApiTemplate.Common.Attributes;
using AdminApiTemplate.Common.Exceptions;
using OfficeOpenXml;
using System.ComponentModel;
using System.Reflection;
using System.Text;

namespace AdminApiTemplate.Common.Helpers
{
    public class ExcelHelper
    {
        private const string columns = "abcdefghijklmnopqrstuvwxyz";

        public static string GetColumnName(int num)
        {
            var sb = new StringBuilder();
            var times = num/columns.Length;
            if (times > columns.Length)
            {
                throw new WithCodeException((int)ErrorCodeEnum.CodeError, "列数过多，请减少一些");
            }
            var idx = num%columns.Length;
            if (idx==0)
            {
                if (times > 1)
                {
                    sb.Append(columns[times-2]);
                }
                sb.Append(columns[columns.Length-1]);
            }
            else
            {
                if (times > 0)
                {
                    sb.Append(columns[times-1]);
                }
                sb.Append(columns[idx-1]);
            }
            return sb.ToString().ToUpper();
        }

        public static void Export<T>(string fullName, List<T> data)
        {
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;

            var ttype = typeof(T);
            var typeName = ttype.Name;
            var typeDescriptionAttr = ttype.GetCustomAttribute<DescriptionAttribute>();
            if (typeDescriptionAttr != null)
            {
                typeName = typeDescriptionAttr.Description;
            }

            var tps = ttype.GetProperties().ToList();
            for (var i = tps.Count-1; i>=0; i--)
            {
                var attr = tps[i].GetCustomAttribute<NotExportColumnAttribute>();
                if (attr != null)
                {
                    tps.RemoveAt(i);
                }
            }

            using (var package = new ExcelPackage(new FileInfo(fullName)))
            {
                var worksheet = package.Workbook.Worksheets.Add(typeName);
                for (var i = 0; i < tps.Count; i++)
                {
                    var tp = tps[i];
                    var tpName = tp.Name;
                    var tpDescriptionAttr = tp.GetCustomAttribute<DescriptionAttribute>();
                    if (tpDescriptionAttr != null)
                    {
                        tpName = tpDescriptionAttr.Description;
                    }

                    worksheet.Cells[1, i + 1].Value = tpName;
                }
                for (var j = 0; j < data.Count; j++)
                {
                    for (var k = 0; k < tps.Count; k++)
                    {
                        var val_display = "";
                        var val = tps[k].GetValue(data[j], null);
                        if (val != null)
                        {
                            val_display=Convert.ToString(val);
                        }
                        worksheet.Cells[j + 2, k + 1].Value = val_display;
                    }
                }
                package.Save();
            }
        }

        /// <summary>
        /// excel导入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="bytes"></param>
        /// <param name="dataRow">数据从第几行开始</param>
        /// <param name="onlyFirstSheet">只导入第一个Sheet的数据</param>
        /// <returns></returns>
        public static List<T> Import<T>(byte[] bytes, int dataRow = 2, bool onlyFirstSheet = true) where T : class, new()
        {
            var ttype = typeof(T);
            var tps = ttype.GetProperties();

            var items = new List<T>();
            using (var ms = new MemoryStream(bytes))
            {
                ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
                using (var package = new ExcelPackage(ms))
                {
                    var sheets = package.Workbook.Worksheets.ToList();
                    if (onlyFirstSheet)
                    {
                        sheets =  new List<ExcelWorksheet> { package.Workbook.Worksheets[0] };
                    }

                    foreach (var sheet in sheets)
                    {
                        for (var i = dataRow; i <= sheet.Dimension.Rows; i++)
                        {
                            var one = Activator.CreateInstance<T>();
                            for (var j = 0; j < tps.Length; j++)
                            {
                                var cell = sheet.Cells[i, j + 1];
                                if (cell == null)
                                {
                                    continue;
                                }

                                var tp = tps[j];
                                var val = GetValue2(tp, cell.GetValue<string>());
                                tp.SetValue(one, val, null);
                            }
                            items.Add(one);
                        }
                    }
                }
            }

            return items;
        }

        private static object GetValue2(PropertyInfo tp, string val)
        {
            if (tp.PropertyType == typeof(int) || tp.PropertyType == typeof(int?))
            {
                if (int.TryParse(val, out int temp))
                {
                    return temp;
                }

                return default(int?);
            }

            if (tp.PropertyType == typeof(long) || tp.PropertyType == typeof(long?))
            {
                if (long.TryParse(val, out long temp))
                {
                    return temp;
                }

                return default(long?);
            }

            if (tp.PropertyType == typeof(decimal) || tp.PropertyType == typeof(decimal?))
            {
                if (decimal.TryParse(val, out decimal temp))
                {
                    return temp;
                }

                return default(decimal?);
            }

            if (tp.PropertyType == typeof(DateTime) || tp.PropertyType == typeof(DateTime?))
            {
                if (DateTime.TryParse(val, out DateTime temp))
                {
                    return temp;
                }

                return default(DateTime?);
            }

            if (tp.PropertyType == typeof(bool) || tp.PropertyType == typeof(bool?))
            {
                if (bool.TryParse(val, out bool temp))
                {
                    return temp;
                }

                return default(bool?);
            }

            return val;
        }

        /// <summary>
        /// 通过模板生成文件
        /// </summary>
        /// <param name="templateFile"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public static string Build(string templateFile, object data)
        {
            var folder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Export");
            if (!Directory.Exists(folder))
            {
                Directory.CreateDirectory(folder);
            }

            var fileName = $"{RandomHelper.RequestId()}.xlsx";
            var fullName = Path.Combine(folder, fileName);

            var bytes = File.ReadAllBytes(templateFile);
            File.WriteAllBytes(fullName, bytes);
            return fullName;
        }
    }
}
